unit URelatorioContrato;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, UrelatorioBase, RLPDFFilter, RLXLSFilter, RLFilters,
  RLHTMLFilter, RLDraftFilter, DB, DBTables, RLReport, StdCtrls, ExtCtrls,
  Buttons, DBClient, Mask, ToolEdit;

type
  TFRelatorioContrato = class(TFrelatorioBase)
    CDSRelatorio: TClientDataSet;
    DtPeriodo1: TDateEdit;
    DateEdit1: TDateEdit;
    DateEdit2: TDateEdit;
    DateEdit3: TDateEdit;
    RLBand3: TRLBand;
    RLLabel1: TRLLabel;
    RLLabel2: TRLLabel;
    RLLabel3: TRLLabel;
    RLLabel4: TRLLabel;
    RLLabel5: TRLLabel;
    RLLabel6: TRLLabel;
    RLGroup1: TRLGroup;
    RLGroup2: TRLGroup;
    RLBand5: TRLBand;
    RLDBText2: TRLDBText;
    RLDBText6: TRLDBText;
    RLDBText7: TRLDBText;
    RLDBText3: TRLDBText;
    RLBand6: TRLBand;
    RLDBText5: TRLDBText;
    RLBand4: TRLBand;
    RLDBText4: TRLDBText;
    RLLabel7: TRLLabel;
    RLLabel8: TRLLabel;
    RLLabel9: TRLLabel;
    RLLabel10: TRLLabel;
    RLLabel11: TRLLabel;
    RLLabel12: TRLLabel;
    RLBand2: TRLBand;
    RLLabel13: TRLLabel;
    RLLabel14: TRLLabel;
    RLBand7: TRLBand;
    RLLabel15: TRLLabel;
    RLLabel16: TRLLabel;
    procedure btnPesquisarClick(Sender: TObject);
    procedure RLDBText3BeforePrint(Sender: TObject; var Text: String;
      var PrintIt: Boolean);
    procedure btnExecutarClick(Sender: TObject);
    procedure RLBand4BeforePrint(Sender: TObject; var PrintIt: Boolean);
    procedure RLBand6BeforePrint(Sender: TObject; var PrintIt: Boolean);
    procedure RLBand5BeforePrint(Sender: TObject; var PrintIt: Boolean);
    procedure RLBand2BeforePrint(Sender: TObject; var PrintIt: Boolean);
    procedure RLBand7BeforePrint(Sender: TObject; var PrintIt: Boolean);
  private
    { Private declarations }
  public
    { Public declarations }

   procedure Criar_tab_temp;
  end;

var
  FRelatorioContrato: TFRelatorioContrato;
  CONTRATO,AREA,SERVICO:string;
  TOTAL_AREA1,TOTAL_CONTRATO1:real;

implementation
 uses UDMGeral, UDmFinanceiro, UDmGestao, UGeral;
{$R *.dfm}

procedure TFRelatorioContrato.Criar_tab_temp;
begin

  if CDSRelatorio.Active then // se estiver ativo...
  begin
    CDSRelatorio.EmptyDataSet;
    CDSRelatorio.Close;
  end;
  CDSRelatorio.FieldDefs.Clear;
  CDSRelatorio.FieldDefs.Add('CONTRATO'               ,ftInteger ,0);
  CDSRelatorio.FieldDefs.Add('CONTRATO_DESC'           ,ftString,60);
  CDSRelatorio.FieldDefs.Add('NOTAFISCAL'              ,ftString,10);
  CDSRelatorio.FieldDefs.Add('AREA_ATUACAO'           ,ftInteger, 0);
  CDSRelatorio.FieldDefs.Add('AREA_ATUACAO_DESC'       ,ftString,60);
  CDSRelatorio.FieldDefs.Add('SERVICO'                ,ftInteger, 0);
  CDSRelatorio.FieldDefs.Add('SERVICO_DESC'           ,ftString,100);
  CDSRelatorio.FieldDefs.Add('ENCARREGADO'            ,ftInteger, 0);
  CDSRelatorio.FieldDefs.Add('ENCARREGADO_DESC'        ,ftString,60);
  CDSRelatorio.FieldDefs.Add('DATA_MES1'                ,ftString,7);
  CDSRelatorio.FieldDefs.Add('VALOR_MES1'              ,ftFloat,  0);
  CDSRelatorio.FieldDefs.Add('DATA_MES2'                ,ftString,7);
  CDSRelatorio.FieldDefs.Add('VALOR_MES2'              ,ftFloat,  0);
  CDSRelatorio.FieldDefs.Add('DATA_MES3'                ,ftString,7);
  CDSRelatorio.FieldDefs.Add('VALOR_MES3'              ,ftFloat,  0);
  CDSRelatorio.FieldDefs.Add('DATA_MES4'                ,ftString,7);
  CDSRelatorio.FieldDefs.Add('VALOR_MES4'              ,ftFloat,  0);
  CDSRelatorio.IndexFieldNames := 'CONTRATO;AREA_ATUACAO';
  CDSRelatorio.CreateDataSet;
  CDSRelatorio.Open;

end;

procedure TFRelatorioContrato.btnPesquisarClick(Sender: TObject);
const  Seleciona_DAdos =
  ' select '#10+
  ' notafiscalvenda.nf_codigo,'#10+
  ' notafiscalvenda.nf_numeronf,'#10+
  ' notafiscalvenda.nf_dataemissao,'#10+
  ' notafiscalvenda.nf_cod_contrato,'#10+
  ' notafiscalvenda.nf_valorfinalnota,'#10+
  ' itensnotafiscalvenda.itensnf_encarregado,'#10+
  ' itensnotafiscalvenda.itensnf_produto,'#10+
  ' itensnotafiscalvenda.itensnf_area_atuacao,'#10+
  ' itensnotafiscalvenda.itensnf_num_pedido_cli,'#10+
  ' itensnotafiscalvenda.itensnf_competencia, '#10+
  ' contrato.con_descricao, '#10+
  ' area_atuacao.are_descricao, '#10+
  ' vendedor.vend_nome, '#10+
  ' produto.prod_descricaosai '#10+ 
  ' from notafiscalvenda '#10+
  ' inner join itensnotafiscalvenda on (itensnotafiscalvenda.itensnf_notafiscal = notafiscalvenda.nf_codigo) '#10+
  ' inner join produto on (itensnotafiscalvenda.itensnf_produto = produto.prod_codigo) '#10+
  ' left join contrato on (notafiscalvenda.nf_cod_contrato = contrato.con_codigo) '#10+
  ' left join area_atuacao on (itensnotafiscalvenda.itensnf_area_atuacao = area_atuacao.are_codigo) '#10+
  ' left join vendedor on (itensnotafiscalvenda.itensnf_encarregado = vendedor.vend_codigo) '#10+
  ' where '#10+
  ' ((notafiscalvenda.excluido <> ''S'')OR (notafiscalvenda.excluido is null))AND '#10+
  ' (notafiscalvenda.codigoempresa = %s) AND '#10+
  ' (EXTRACT(month FROM notafiscalvenda.nf_dataemissao) = %s AND '#10+
  ' EXTRACT(YEAR FROM notafiscalvenda.nf_dataemissao) = %s) '#10+
  ' ORDER BY '#10+
  ' notafiscalvenda.nf_cod_contrato, '#10+
  ' itensnotafiscalvenda.itensnf_area_atuacao, '#10+
  ' itensnotafiscalvenda.itensnf_produto, '#10+
  ' itensnotafiscalvenda.itensnf_encarregado ';

var
MES,ANO:string;
ACHOU:Boolean;
begin
  RLMemo.Lines.Clear;

// inherited;
 MES:='0';
 ANO:='0';
 Criar_tab_temp;

// MES 1 --------------
 if DtPeriodo1.Date <> 0 then
 begin
    Mes:= FormatDateTime('MM',DtPeriodo1.Date);
    ANO:= FormatDateTime('YYYY',DtPeriodo1.Date);
    QPesquisa.Close;
    QPesquisa.SQL.Clear;
    QPesquisa.SQL.Add(format(Seleciona_DAdos,[IntToStr(Codigoempresa),MES,ANO]));
    QPesquisa.Open;
    QPesquisa.First;
   while NOT(QPesquisa.Eof) do
   begin
      if NOT(CDSRelatorio.IsEmpty) then
      begin
        CDSRelatorio.First;
        ACHOU:=false;
        while NOT(CDSRelatorio.Eof) do
        begin
          if (CDSRelatorio.FieldByName('CONTRATO').AsInteger     =  QPesquisa.FieldByName('nf_cod_contrato').AsInteger)AND
             (CDSRelatorio.FieldByName('AREA_ATUACAO').AsInteger =  QPesquisa.FieldByName('itensnf_area_atuacao').AsInteger)AND
             (CDSRelatorio.FieldByName('SERVICO').AsInteger      =  QPesquisa.FieldByName('itensnf_produto').AsInteger)AND
             (CDSRelatorio.FieldByName('ENCARREGADO').AsInteger  =  QPesquisa.FieldByName('itensnf_encarregado').AsInteger) then
          begin
            CDSRelatorio.Edit;
            CDSRelatorio.FieldByName('VALOR_MES1').AsFloat:=
            CDSRelatorio.FieldByName('VALOR_MES1').AsFloat + QPesquisa.FieldByName('nf_valorfinalnota').AsFloat;
            CDSRelatorio.post;
            ACHOU:=true;
            CDSRelatorio.Last;
          end else begin
            CDSRelatorio.Next;
          end;
        end;
          if achou = false then
          begin
           CDSRelatorio.Insert;
           CDSRelatorio.FieldByName('CONTRATO_DESC').AsString :=QPesquisa.FieldByName('con_descricao').AsString;
           CDSRelatorio.FieldByName('AREA_ATUACAO_DESC').AsString:=QPesquisa.FieldByName('are_descricao').AsString;
           CDSRelatorio.FieldByName('ENCARREGADO_DESC').AsString:=QPesquisa.FieldByName('vend_nome').AsString;
           CDSRelatorio.FieldByName('SERVICO_DESC').AsString:=QPesquisa.FieldByName('prod_descricaosai').AsString;           
           CDSRelatorio.FieldByName('NOTAFISCAL').AsString    :=  QPesquisa.FieldByName('nf_numeronf').AsString;
           CDSRelatorio.FieldByName('CONTRATO').AsInteger     :=  QPesquisa.FieldByName('nf_cod_contrato').AsInteger;
           CDSRelatorio.FieldByName('AREA_ATUACAO').AsInteger :=  QPesquisa.FieldByName('itensnf_area_atuacao').AsInteger;
           CDSRelatorio.FieldByName('SERVICO').AsInteger      :=  QPesquisa.FieldByName('itensnf_produto').AsInteger;
           CDSRelatorio.FieldByName('ENCARREGADO').AsInteger  :=  QPesquisa.FieldByName('itensnf_encarregado').AsInteger;
           CDSRelatorio.FieldByName('VALOR_MES1').AsFloat     :=  QPesquisa.FieldByName('nf_valorfinalnota').AsFloat;
           CDSRelatorio.FieldByName('DATA_MES1').AsString     :=  FormatDateTime('MM/YYYY',QPesquisa.FieldByName('nf_dataemissao').AsDateTime);
           CDSRelatorio.Append;
          end;
      end else begin
            CDSRelatorio.Insert;
            CDSRelatorio.FieldByName('CONTRATO_DESC').AsString :=QPesquisa.FieldByName('con_descricao').AsString;
            CDSRelatorio.FieldByName('AREA_ATUACAO_DESC').AsString:=QPesquisa.FieldByName('are_descricao').AsString;
            CDSRelatorio.FieldByName('ENCARREGADO_DESC').AsString:=QPesquisa.FieldByName('vend_nome').AsString;
            CDSRelatorio.FieldByName('SERVICO_DESC').AsString:=QPesquisa.FieldByName('prod_descricaosai').AsString;
            CDSRelatorio.FieldByName('NOTAFISCAL').AsString    :=  QPesquisa.FieldByName('nf_numeronf').AsString;
            CDSRelatorio.FieldByName('CONTRATO').AsInteger     :=  QPesquisa.FieldByName('nf_cod_contrato').AsInteger;
            CDSRelatorio.FieldByName('AREA_ATUACAO').AsInteger :=  QPesquisa.FieldByName('itensnf_area_atuacao').AsInteger;
            CDSRelatorio.FieldByName('SERVICO').AsInteger      :=  QPesquisa.FieldByName('itensnf_produto').AsInteger;
            CDSRelatorio.FieldByName('ENCARREGADO').AsInteger  :=  QPesquisa.FieldByName('itensnf_encarregado').AsInteger;
            CDSRelatorio.FieldByName('VALOR_MES1').AsFloat     :=  QPesquisa.FieldByName('nf_valorfinalnota').AsFloat;
            CDSRelatorio.FieldByName('DATA_MES1').AsString     :=  FormatDateTime('MM/YYYY',QPesquisa.FieldByName('nf_dataemissao').AsDateTime);
            CDSRelatorio.Append;
      end;
    QPesquisa.Next;
   end;
 end;



  if not(CDSRelatorio.IsEmpty) then
  begin
    btnExecutar.Enabled := true;
    btnExecutar.SetFocus;
  end else
    btnExecutar.Enabled := false;

  lbResultado.Caption := 'Resultado da Pesquisa: ' + IntToStr(CDSRelatorio.RecordCount);

end;

procedure TFRelatorioContrato.RLDBText3BeforePrint(Sender: TObject;
  var Text: String; var PrintIt: Boolean);
begin
  inherited;
  TEXT := FormatFloat('0.,00',strtofloat(TEXT));
end;

procedure TFRelatorioContrato.btnExecutarClick(Sender: TObject);
begin
  CONTRATO   :='';
  AREA       :='';
  SERVICO    :='';
  TOTAL_AREA1:=0;
  TOTAL_CONTRATO1:=0;
  inherited;

end;

procedure TFRelatorioContrato.RLBand4BeforePrint(Sender: TObject;
  var PrintIt: Boolean);
begin
  inherited;
 if CONTRATO <> '' then
 begin
   if CONTRATO = CDSRelatorio.FieldByName('CONTRATO_DESC').AsString then
   begin
    RLDBText4.Visible:=false;
    RLBand4.Height:=0;
   end else  begin
    RLDBText4.Visible:=true;
    CONTRATO:= CDSRelatorio.FieldByName('CONTRATO_DESC').AsString;
    RLBand4.Height:=24;
   end;
 end else begin
    RLDBText4.Visible:=true;
    CONTRATO:= CDSRelatorio.FieldByName('CONTRATO_DESC').AsString;
    RLBand4.Height:=24;
 end;


end;

procedure TFRelatorioContrato.RLBand6BeforePrint(Sender: TObject;
  var PrintIt: Boolean);
begin
  inherited;

 if AREA <> '' then
 begin
   if (AREA = CDSRelatorio.FieldByName('AREA_ATUACAO_DESC').AsString)AND (RLDBText4.Visible = false ) then
   begin
    RLDBText5.Visible:=false;
    RLBand6.Height:=0;
   end else  begin
    RLDBText5.Visible:=true;
    AREA:= CDSRelatorio.FieldByName('AREA_ATUACAO_DESC').AsString;
    RLBand6.Height:=24;
   end;
 end else begin
    RLDBText5.Visible:=true;
    AREA:= CDSRelatorio.FieldByName('AREA_ATUACAO_DESC').AsString;
    RLBand6.Height:=24;
 end;


end;

procedure TFRelatorioContrato.RLBand5BeforePrint(Sender: TObject;
  var PrintIt: Boolean);
begin
  inherited;

  TOTAL_AREA1:=TOTAL_AREA1+CDSRelatorio.FieldByName('VALOR_MES1').AsFloat;
  TOTAL_CONTRATO1:=TOTAL_CONTRATO1+CDSRelatorio.FieldByName('VALOR_MES1').AsFloat;

end;

procedure TFRelatorioContrato.RLBand2BeforePrint(Sender: TObject;
  var PrintIt: Boolean);
begin
  inherited;

  RLLabel13.Caption:= FormatFloat('0,.00',TOTAL_AREA1);

  TOTAL_AREA1:=0;

end;

procedure TFRelatorioContrato.RLBand7BeforePrint(Sender: TObject;
  var PrintIt: Boolean);
begin
  inherited;

  RLLabel15.Caption:= FormatFloat('0,.00',TOTAL_CONTRATO1);

  TOTAL_CONTRATO1:=0;

end;

end.
